SherbyNet - /page/wikibook/entry.mu

Micron Document

<!DOCTYPE html>

<title>Isolation (database systems)</title>

</head>

<span id="openzim-page-title" class="mw-page-title-main"><span class="mw-page-title-main">Isolation (database systems)</span></span>

</h1>

</header>

/* start https://en.wikipedia.org/ */

.mw-parser-output .ambox{border:1px solid #a2a9b1;border-left:10px solid #36c;background-color:#fbfbfb;box-sizing:border-box}.mw-parser-output .ambox+link+.ambox,.mw-parser-output .ambox+link+style+.ambox,.mw-parser-output .ambox+link+link+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+style+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+link+.ambox{margin-top:-1px}html body.mediawiki .mw-parser-output .ambox.mbox-small-left{margin:4px 1em 4px 0;overflow:hidden;width:238px;border-collapse:collapse;font-size:88%;line-height:1.25em}.mw-parser-output .ambox-speedy{border-left:10px solid #b32424;background-color:#fee7e6}.mw-parser-output .ambox-delete{border-left:10px solid #b32424}.mw-parser-output .ambox-content{border-left:10px solid #f28500}.mw-parser-output .ambox-style{border-left:10px solid #fc3}.mw-parser-output .ambox-move{border-left:10px solid #9932cc}.mw-parser-output .ambox-protection{border-left:10px solid #a2a9b1}.mw-parser-output .ambox .mbox-text{border:none;padding:0.25em 0.5em;width:100%}.mw-parser-output .ambox .mbox-image{border:none;padding:2px 0 2px 0.5em;text-align:center}.mw-parser-output .ambox .mbox-imageright{border:none;padding:2px 0.5em 2px 0;text-align:center}.mw-parser-output .ambox .mbox-empty-cell{border:none;padding:0;width:1px}.mw-parser-output .ambox .mbox-image-div{width:52px}@media(min-width:720px){.mw-parser-output .ambox{margin:0 10%}}@media print{body.ns-0 .mw-parser-output .ambox{display:none!important}}

/* end https://en.wikipedia.org/ */

</style>

<p>In <a href="Database" title="Database">database</a> systems, <b>isolation</b> is one of the <a href="ACID" title="ACID">ACID</a> (<i><a href="Atomicity_(database_systems)" title="Atomicity (database systems)">Atomicity</a>, <a href="Consistency_(database_systems)" title="Consistency (database systems)">Consistency</a>, Isolation, <a href="Durability_(database_systems)" title="Durability (database systems)">Durability</a></i>) <a href="Database_transaction" title="Database transaction">transaction</a> properties. It determines how <a href="Database_transaction" title="Database transaction">transaction</a> integrity is visible to other users and systems. A lower isolation level increases the ability of many users to access the same data at the same time, but also increases the number of <a href="Concurrency_(computer_science)" title="Concurrency (computer science)">concurrency</a> effects (such as <a href="Write%E2%80%93read_conflict" title="Write–read conflict"> dirty reads</a> or <a href="Lost_update" class="mw-redirect" title="Lost update">lost updates</a>) users might encounter. Conversely, a higher isolation level reduces the types of concurrency effects that users may encounter, but requires more system resources and increases the chances that one transaction will block another.<sup id="cite_ref-1" class="reference"><a href="#cite_note-1"><span class="cite-bracket">[</span>1<span class="cite-bracket">]</span></a></sup>

</p>

<div class="mw-heading mw-heading2"><h2 id="DBMS_concurrency_control">DBMS concurrency control</h2></div>

<p><a href="Concurrency_control" title="Concurrency control">Concurrency control</a> comprises the underlying mechanisms in a <a href="DBMS" class="mw-redirect" title="DBMS">DBMS</a> which handle isolation and guarantee related correctness. It is heavily used by the database and storage engines both to guarantee the correct execution of concurrent transactions, and (via different mechanisms) the correctness of other DBMS processes. The transaction-related mechanisms typically constrain the database data access operations' timing (<a href="Database_transaction_schedule" title="Database transaction schedule">transaction schedules</a>) to certain orders characterized as the <a href="Serializability" class="mw-redirect" title="Serializability">serializability</a> and <a href="Recoverability" class="mw-redirect" title="Recoverability">recoverability</a> schedule properties. Constraining database access operation execution typically means reduced performance (measured by rates of execution), and thus concurrency control mechanisms are typically designed to provide the best performance possible under the constraints. Often, when possible without harming correctness, the serializability property is compromised for better performance. However, recoverability cannot be compromised, since such typically results in a <a href="Data_integrity" title="Data integrity">database integrity violation</a>.

</p><p><a href="Two-phase_locking" title="Two-phase locking">Two-phase locking</a> is the most common transaction concurrency control method in DBMSs, used to provide both serializability and recoverability for correctness. In order to access a database object a transaction first needs to acquire a <a href="Lock_(database)" class="mw-redirect" title="Lock (database)">lock</a> for this object. Depending on the access operation type (e.g., reading or writing an object) and on the lock type, acquiring the lock may be blocked and postponed, if another transaction is holding a lock for that object.

</p>

<div class="mw-heading mw-heading2"><h2 id="Client-side_isolation">Client-side isolation</h2></div>

<p>Isolation is typically enforced at the database level. However, various client-side systems can also be used. It can be controlled in application frameworks or runtime containers such as <a href="J2EE" class="mw-redirect" title="J2EE">J2EE</a> <a href="Entity_Bean" title="Entity Bean">Entity Beans</a><sup id="cite_ref-Stony_Brook_2-0" class="reference"><a href="#cite_note-Stony_Brook-2"><span class="cite-bracket">[</span>2<span class="cite-bracket">]</span></a></sup> On older systems, it may be implemented systemically (by the application developers), for example through the use of temporary tables. In two-tier, three-tier, or <a href="Multitier_architecture" title="Multitier architecture">n-tier</a> web applications a <a href="Transaction_manager" class="mw-redirect" title="Transaction manager">transaction manager</a> can be used to maintain isolation. A transaction manager is <a href="Middleware_(distributed_applications)" title="Middleware (distributed applications)">middleware</a> which sits between an app service (back-end application service) and the <a href="Operating_system" title="Operating system">operating system</a>. A transaction manager can provide global isolation and atomicity. It tracks when new servers join a transaction and coordinates an <a href="Atomic_commit" title="Atomic commit">atomic commit</a> protocol among the servers. The details are abstracted from the app, making transactions simpler and easier to code. A <a href="Transaction_processing_system" title="Transaction processing system">transaction processing monitor</a> (TPM) is a collection of middle-ware including a transaction manager. A TPM might provide local isolation to an app with a lock manager.<sup id="cite_ref-Stony_Brook_2-1" class="reference"><a href="#cite_note-Stony_Brook-2"><span class="cite-bracket">[</span>2<span class="cite-bracket">]</span></a></sup>

</p>

<div class="mw-heading mw-heading2"><h2 id="Read_phenomena">Read phenomena</h2></div>

<p>The ANSI/ISO standard SQL 92 refers to three different <i>read phenomena</i> when a transaction retrieves data that another transaction might have updated.

</p><p>In the following examples, two transactions take place. In transaction 1, a query is performed, then in transaction 2, an update is performed, and finally in transaction 1, the same query is performed again.

</p><p>The examples use the following relation:

</p>

<caption>users

</caption>

<th>age

</th></tr>

<tr>

<td>Alice</td>

<td>20

</td></tr>

<tr>

<td>25

</td></tr></tbody></table>

<div class="mw-heading mw-heading3"><h3 id="Dirty_reads">Dirty reads</h3></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .hatnote{font-style:italic}.mw-parser-output div.hatnote{padding-left:1.6em;margin-bottom:0.5em}.mw-parser-output .hatnote i{font-style:normal}.mw-parser-output .hatnote+link+.hatnote{margin-top:-0.5em}@media print{body.ns-0 .mw-parser-output .hatnote{display:none!important}}

/* end https://en.wikipedia.org/ */

</style><div role="note" class="hatnote navigation-not-searchable">Main article: <a href="Write-read_conflict" class="mw-redirect" title="Write-read conflict">Write-read conflict</a></div>

<p>A <i>dirty read</i> (aka <i>uncommitted dependency</i>) occurs when a transaction retrieves a row that has been updated by another transaction that is not yet committed.

</p><p>In this example, transaction 1 retrieves the row with id 1, then transaction 2 updates the row with id 1, and finally transaction 1 retrieves the row with id 1 again. Now if transaction 2 rolls back its update (already retrieved by transaction 1) or performs other updates, then the view of the row may be wrong in transaction 1. At the READ UNCOMMITTED isolation level, the second SELECT in transaction 1 retrieves the updated row: this is a dirty read. At the READ COMMITTED, REPEATABLE READ, and SERIALIZABLE isolation levels, the second SELECT in transaction 1 retrieves the initial row.

</p>

<table>

<th>Transaction 1

</th>

<th>Transaction 2

</th></tr>

<tr>

<td><div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span class="k">BEGIN</span><span class="p">;</span>

<span class="k">SELECT</span><span class="w"> </span><span class="n">age</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">users</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">1</span><span class="p">;</span>

<span class="c1">-- retrieves 20</span>

</pre></div>

</td>

<td>

</td></tr>

<tr>

<td>

</td>

<td><div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span class="k">BEGIN</span><span class="p">;</span>

<span class="k">UPDATE</span><span class="w"> </span><span class="n">users</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="n">age</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">21</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">1</span><span class="p">;</span>

<span class="c1">-- no commit here</span>

</pre></div>

</td></tr>

<tr>

<td><div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span class="k">SELECT</span><span class="w"> </span><span class="n">age</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">users</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">1</span><span class="p">;</span>

<span class="c1">-- READ UNCOMMITTED retrieves 21 (dirty read)</span>

<span class="c1">-- READ COMMITTED retrieves 20 (dirty read has been avoided)</span>

<span class="c1">-- REPEATABLE READ retrieves 20 (dirty read has been avoided)</span>

<span class="c1">-- SERIALIZABLE retrieves 20 (dirty read has been avoided)</span>

<span class="k">COMMIT</span><span class="p">;</span>

</pre></div>

</td>

<td>

</td></tr>

<tr>

<td>

</td>

<td><div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span class="k">ROLLBACK</span><span class="p">;</span>

</pre></div>

</td></tr></tbody></table>

<div class="mw-heading mw-heading3"><h3 id="Non-repeatable_reads">Non-repeatable reads</h3></div>

<p>A <i>non-repeatable read</i> occurs when a transaction retrieves a row twice and that row is updated by another transaction that is committed in between.

</p><p>In this example, transaction 1 retrieves the row with id 1, then transaction 2 updates the row with id 1 and is committed, and finally transaction 1 retrieves the row with id 1 again. At the READ UNCOMMITTED and READ COMMITTED isolation levels, the second SELECT in transaction 1 retrieves the updated row: this is a non-repeatable read. At the REPEATABLE READ and SERIALIZABLE isolation levels, the second SELECT in transaction 1 retrieves the initial row.

</p>

<table>

<th>Transaction 1

</th>

<th>Transaction 2

</th></tr>

<tr>

<td><div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span class="k">BEGIN</span><span class="p">;</span>

<span class="c1">-- retrieves 20</span>

</pre></div>

</td>

<td>

</td></tr>

<tr>

<td>

</td>

<td><div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span class="k">BEGIN</span><span class="p">;</span>

<span class="k">COMMIT</span><span class="p">;</span>

</pre></div>

</td></tr>

<tr>

<span class="c1">-- READ UNCOMMITTED retrieves 21 (non-repeatable read)</span>

<span class="c1">-- READ COMMITTED retrieves 21 (non-repeatable read)</span>

<span class="c1">-- REPEATABLE READ retrieves 20 (non-repeatable read has been avoided)</span>

<span class="c1">-- SERIALIZABLE retrieves 20 (non-repeatable read has been avoided)</span>

<span class="k">COMMIT</span><span class="p">;</span>

</pre></div>

</td></tr></tbody></table>

<div class="mw-heading mw-heading3"><h3 id="Phantom_reads">Phantom reads</h3></div>

<p>A <i>phantom read</i> occurs when a transaction retrieves a set of rows twice and new rows are inserted into or removed from that set by another transaction that is committed in between.

</p><p>In this example, transaction 1 retrieves the set of rows with age greater than 17, then transaction 2 inserts a row with age 26 and is committed, and finally transaction 1 retrieves the set of rows with age greater than 17 again. At the READ UNCOMMITTED, READ COMMITTED, and REPEATABLE READ isolation levels, the second SELECT in transaction 1 retrieves the new set of rows that includes the inserted row: this is a phantom read. At the SERIALIZABLE isolation level, the second SELECT in transaction 1 retrieves the initial set of rows.

</p>

<table>

<th>Transaction 1

</th>

<th>Transaction 2

</th></tr>

<tr>

<td><div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span class="k">BEGIN</span><span class="p">;</span>

<span class="k">SELECT</span><span class="w"> </span><span class="n">name</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">users</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">age</span><span class="w"> </span><span class="o">></span><span class="w"> </span><span class="mi">17</span><span class="p">;</span>

<span class="c1">-- retrieves Alice and Bob</span>

</pre></div>

</td>

<td>

</td></tr>

<tr>

<td>

</td>

<td><div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span class="k">BEGIN</span><span class="p">;</span>

<span class="k">INSERT</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">users</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="mi">3</span><span class="p">,</span><span class="w"> </span><span class="s1">'Carol'</span><span class="p">,</span><span class="w"> </span><span class="mi">26</span><span class="p">);</span>

<span class="k">COMMIT</span><span class="p">;</span>

</pre></div>

</td></tr>

<tr>

<td><div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span class="k">SELECT</span><span class="w"> </span><span class="n">name</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">users</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">age</span><span class="w"> </span><span class="o">></span><span class="w"> </span><span class="mi">17</span><span class="p">;</span>

<span class="c1">-- READ UNCOMMITTED retrieves Alice, Bob and Carol (phantom read)</span>

<span class="c1">-- READ COMMITTED retrieves Alice, Bob and Carol (phantom read)</span>

<span class="c1">-- REPEATABLE READ retrieves Alice, Bob and Carol (phantom read)</span>

<span class="c1">-- SERIALIZABLE retrieves Alice and Bob (phantom read has been avoided)</span>

<span class="k">COMMIT</span><span class="p">;</span>

</pre></div>

</td></tr></tbody></table>

<p>There are two basic strategies used to prevent non-repeatable reads and phantom reads. In the first strategy, <i>lock-based concurrency control</i>, transaction 2 is committed after transaction 1 is committed or rolled back. It produces the serial <a href="Database_transaction_schedule" title="Database transaction schedule">schedule</a> <i>T1, T2</i>. In the other strategy, <i><a href="Multiversion_concurrency_control" title="Multiversion concurrency control">multiversion concurrency control</a></i>, transaction 2 is committed immediately while transaction 1, which started before transaction 2, continues to operate on an old snapshot of the database taken at the start of transaction 1, and when transaction 1 eventually tries to commit, if the result of committing would be equivalent to the serial schedule <i>T1, T2</i>, then transaction 1 is committed; otherwise, there is a commit conflict and transaction 1 is rolled back with a serialization failure.

</p><p>Under lock-based concurrency control, non-repeatable reads and phantom reads may occur when read locks are not acquired when performing a SELECT, or when the acquired locks on affected rows are released as soon as the SELECT is performed. Under multiversion concurrency control, non-repeatable reads and phantom reads may occur when the requirement that a transaction affected by a commit conflict must be rolled back is relaxed.

</p>

<div class="mw-heading mw-heading2"><h2 id="Isolation_levels">Isolation levels</h2></div>

<p>Of the four <a href="ACID" title="ACID">ACID</a> properties in a <a href="Database_management_system" class="mw-redirect" title="Database management system">DBMS</a> (Database Management System), the isolation property is the one most often relaxed. When attempting to maintain the highest level of isolation, a DBMS usually acquires <a href="Lock_(database)" class="mw-redirect" title="Lock (database)">locks</a> on data which may result in a loss of <a href="Concurrency_(computer_science)" title="Concurrency (computer science)">concurrency</a>, or implements <a href="Multiversion_concurrency_control" title="Multiversion concurrency control">multiversion concurrency control</a>. This requires adding logic for the <a href="Software_application" class="mw-redirect" title="Software application">application</a> to function correctly.

</p><p>Most DBMSs offer a number of <i>transaction isolation levels</i>, which control the degree of locking that occurs when selecting data. For many database applications, the majority of database transactions can be constructed to avoid requiring high isolation levels (e.g. SERIALIZABLE level), thus reducing the locking overhead for the system. The programmer must carefully analyze database access code to ensure that any relaxation of isolation does not cause software bugs that are difficult to find. Conversely, if higher isolation levels are used, the possibility of <a href="Deadlock_(computer_science)" title="Deadlock (computer science)">deadlock</a> is increased, which also requires careful analysis and programming techniques to avoid.

</p><p>Since each isolation level is stronger than those below, in that no higher isolation level allows an action forbidden by a lower one, the standard permits a DBMS to run a transaction at an isolation level stronger than that requested (e.g., a "Read committed" transaction may actually be performed at a "Repeatable read" isolation level).

</p><p>The isolation levels defined by the <a href="American_National_Standards_Institute" title="American National Standards Institute">ANSI</a>/<a href="International_Organization_for_Standardization" title="International Organization for Standardization">ISO</a> <a href="SQL" title="SQL">SQL</a> standard are listed as follows.

</p>

<div class="mw-heading mw-heading3"><h3 id="Serializable">Serializable</h3></div>

<p>This is the <i>highest</i> isolation level.

</p><p>With a lock-based <a href="Concurrency_control" title="Concurrency control">concurrency control</a> DBMS implementation, <a href="Serializability" class="mw-redirect" title="Serializability">serializability</a> requires read and write locks (acquired on selected data) to be released at the end of the transaction. Also <i>range-locks</i> must be acquired when a <a href="Select_(SQL)" title="Select (SQL)">SELECT</a> query uses a ranged <i>WHERE</i> clause, especially to avoid the <i><b><a href="#Phantom_reads">phantom reads</a></b></i> phenomenon.

</p><p>When using non-lock based concurrency control, no locks are acquired; however, if the system detects a <i>write collision</i> among several concurrent transactions, only one of them is allowed to commit. See <i><a href="Snapshot_isolation" title="Snapshot isolation">snapshot isolation</a></i> for more details on this topic.

</p><p>From : (Second Informal Review Draft) ISO/IEC 9075:1992, Database Language SQL- July 30, 1992:

<i>The execution of concurrent SQL-transactions at isolation level SERIALIZABLE is guaranteed to be serializable. A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions. A serial execution is one in which each SQL-transaction executes to completion before the next SQL-transaction begins.</i>

</p>

<div class="mw-heading mw-heading3"><h3 id="Repeatable_reads">Repeatable reads</h3></div>

<p>In this isolation level, a lock-based <a href="Concurrency_control" title="Concurrency control">concurrency control</a> DBMS implementation keeps read and write locks (acquired on selected data) until the end of the transaction. However, <i>range-locks</i> are not managed, so <i><b><a href="#Phantom_reads">phantom reads</a></b></i> can occur.

</p><p>Write skew is possible at this isolation level in some systems. Write skew is a phenomenon where two writes are allowed to the same column(s) in a table by two different writers (who have previously read the columns they are updating), resulting in the column having data that is a mix of the two transactions.<sup id="cite_ref-3" class="reference"><a href="#cite_note-3"><span class="cite-bracket">[</span>3<span class="cite-bracket">]</span></a></sup><sup id="cite_ref-4" class="reference"><a href="#cite_note-4"><span class="cite-bracket">[</span>4<span class="cite-bracket">]</span></a></sup>

</p>

<div class="mw-heading mw-heading3"><h3 id="Read_committed">Read committed</h3></div>

<p>In this isolation level, a lock-based <a href="Concurrency_control" title="Concurrency control">concurrency control</a> DBMS implementation keeps write locks (acquired on selected data) until the end of the transaction, but read locks are released as soon as the <a href="Select_(SQL)" title="Select (SQL)">SELECT</a> operation is performed (so the <i><b><a href="#Non-repeatable_reads">non-repeatable reads phenomenon</a></b></i> can occur in this isolation level). As in the previous level, <i>range-locks</i> are not managed.

</p><p>Putting it in simpler words, read committed is an isolation level that guarantees that any data read is committed at the moment it is read. It simply restricts the reader from seeing any intermediate, uncommitted, 'dirty' read. It makes no promise whatsoever that if the transaction re-issues the read, it will find the same data; data is free to change after it is read.

</p>

<div class="mw-heading mw-heading3"><h3 id="Read_uncommitted">Read uncommitted</h3></div>

<p>This is the <i>lowest</i> isolation level. In this level, <i><b><a href="#Dirty_reads">dirty reads</a></b></i> are allowed, so one transaction may see <i>not-yet-committed</i> changes made by other transactions.

</p>

<div class="mw-heading mw-heading2"><h2 id="Default_isolation_level">Default isolation level</h2></div>

<p>The <i>default isolation level</i> of different <a href="Database_management_system" class="mw-redirect" title="Database management system">DBMS</a>'s varies quite widely. Most databases that feature transactions allow the user to set any isolation level. Some DBMS's also require additional syntax when performing a SELECT statement to acquire locks (e.g. <i>SELECT ... FOR UPDATE</i> to acquire exclusive write locks on accessed rows).

</p><p>However, the definitions above have been criticized as being ambiguous, and as not accurately reflecting the isolation provided by many databases:

</p>

<dl><dd>This paper shows a number of weaknesses in the anomaly approach to defining isolation levels. The three ANSI phenomena are ambiguous, and even in their loosest interpretations do not exclude some anomalous behavior ... This leads to some counter-intuitive results. In particular, lock-based isolation levels have different characteristics than their ANSI equivalents. This is disconcerting because commercial database systems typically use locking implementations. Additionally, the ANSI phenomena do not distinguish between a number of types of isolation level behavior that are popular in commercial systems.<sup id="cite_ref-sql-isolation_5-0" class="reference"><a href="#cite_note-sql-isolation-5"><span class="cite-bracket">[</span>5<span class="cite-bracket">]</span></a></sup></dd></dl>

<p>There are also other criticisms concerning ANSI SQL's isolation definition, in that it encourages implementors to do "bad things":

</p>

<dl><dd>... it relies in subtle ways on an assumption that a locking schema is used for concurrency control, as opposed to an optimistic or multi-version concurrency scheme. This implies that the proposed semantics are <i>ill-defined</i>.<sup id="cite_ref-6" class="reference"><a href="#cite_note-6"><span class="cite-bracket">[</span>6<span class="cite-bracket">]</span></a></sup></dd></dl>

<div class="mw-heading mw-heading2"><h2 id="Isolation_levels_vs_read_phenomena">Isolation levels vs read phenomena</h2></div>

<th style="background:var(--background-color-neutral,#eaecf0);color:inherit;background:linear-gradient(to top right,var(--background-color-neutral,#eaecf0) 49%,var(--border-color-base,#a2a9b1) 49.5%,var(--border-color-base,#a2a9b1) 50.5%,var(--background-color-neutral,#eaecf0) 51%);line-height:1.2;padding:0.1em 0.4em;"><div style="margin-left:2em;text-align:right">Read phenomenon</div><div style="margin-right:2em;text-align:left">Isolation level</div></th>

<th>Dirty read</th>

<th>Non-repeatable read</th>

<th>Phantom read

</th></tr>

<tr>

<td>Serializable</td>

<td style="background: #BFD; color:black; vertical-align: middle; text-align: center;" class="yes2 table-yes2">no

</td></tr>

<tr>

<td>Repeatable read</td>

<td style="background:#FFC7C7;color:black;vertical-align:middle;text-align:center;" class="table-no">yes

</td></tr>

<tr>

<td>Read committed</td>

<td style="background:#FFC7C7;color:black;vertical-align:middle;text-align:center;" class="table-no">yes

</td></tr>

<tr>

<td>Read uncommitted</td>

<td style="background:#FFC7C7;color:black;vertical-align:middle;text-align:center;" class="table-no">yes

</td></tr></tbody></table>

<p>Anomaly serializable is not the same as serializable. That is, it is necessary, but not sufficient that a serializable schedule should be free of all three phenomena types.<sup id="cite_ref-sql-isolation_5-1" class="reference"><a href="#cite_note-sql-isolation-5"><span class="cite-bracket">[</span>5<span class="cite-bracket">]</span></a></sup>

</p>

<ul><li><a href="Atomicity_(database_systems)" title="Atomicity (database systems)">Atomicity</a></li>

<li><a href="Consistency_(database_systems)" title="Consistency (database systems)">Consistency</a></li>

<li><a href="Durability_(database_systems)" title="Durability (database systems)">Durability</a></li>

<li><a href="Lock_(database)" class="mw-redirect" title="Lock (database)">Lock (database)</a></li>

<li><a href="Optimistic_concurrency_control" title="Optimistic concurrency control">Optimistic concurrency control</a></li>

<li><a href="Relational_Database_Management_System" class="mw-redirect" title="Relational Database Management System">Relational Database Management System</a></li>

<li><a href="Snapshot_isolation" title="Snapshot isolation">Snapshot isolation</a></li></ul>

<div class="mw-heading mw-heading2"><h2 id="References">References</h2></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .reflist{margin-bottom:0.5em;list-style-type:decimal}@media screen{.mw-parser-output .reflist{font-size:90%}}.mw-parser-output .reflist .references{font-size:100%;margin-bottom:0;list-style-type:inherit}.mw-parser-output .reflist-columns-2{column-width:30em}.mw-parser-output .reflist-columns-3{column-width:25em}.mw-parser-output .reflist-columns{margin-top:0.3em}.mw-parser-output .reflist-columns ol{margin-top:0}.mw-parser-output .reflist-columns li{page-break-inside:avoid;break-inside:avoid-column}.mw-parser-output .reflist-upper-alpha{list-style-type:upper-alpha}.mw-parser-output .reflist-upper-roman{list-style-type:upper-roman}.mw-parser-output .reflist-lower-alpha{list-style-type:lower-alpha}.mw-parser-output .reflist-lower-greek{list-style-type:lower-greek}.mw-parser-output .reflist-lower-roman{list-style-type:lower-roman}

/* end https://en.wikipedia.org/ */

</style><div class="reflist">

<li id="cite_note-1"><span class="mw-cite-backlink"><b><a href="#cite_ref-1">^</a></b></span> <span class="reference-text">"Isolation Levels in the Database Engine", TechNet, Microsoft, <a rel="nofollow" class="external free" href="https://technet.microsoft.com/en-us/library/ms189122(v=SQL.105).aspx">https://technet.microsoft.com/en-us/library/ms189122(v=SQL.105).aspx</a></span>

</li>

<li id="cite_note-Stony_Brook-2"><span class="mw-cite-backlink">^ <a href="#cite_ref-Stony_Brook_2-0"><sup><i><b>a</b></i></sup></a> <a href="#cite_ref-Stony_Brook_2-1"><sup><i><b>b</b></i></sup></a></span> <span class="reference-text">"The Architecture of Transaction Processing Systems", Chapter 23, Evolution of Processing Systems, Department of Computer Science, Stony Brook University, retrieved 20 March 2014, <a rel="nofollow" class="external free" href="http://www.cs.sunysb.edu/~liu/cse315/23.pdf">http://www.cs.sunysb.edu/~liu/cse315/23.pdf</a></span>

</li>

/* start https://en.wikipedia.org/ */

.mw-parser-output cite.citation{font-style:inherit;word-wrap:break-word}.mw-parser-output .citation q{quotes:"\"""\"""'""'"}.mw-parser-output .citation:target{background-color:rgba(0,127,255,0.133)}.mw-parser-output .id-lock-free.id-lock-free a{background:url("./mw/Lock-green.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-limited.id-lock-limited a,.mw-parser-output .id-lock-registration.id-lock-registration a{background:url("./mw/Lock-gray-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-subscription.id-lock-subscription a{background:url("./mw/Lock-red-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .cs1-ws-icon a{background:url("./mw/Wikisource-logo.svg")right 0.1em center/12px no-repeat}body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-free a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-limited a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-registration a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-subscription a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .cs1-ws-icon a{background-size:contain;padding:0 1em 0 0}.mw-parser-output .cs1-code{color:inherit;background:inherit;border:none;padding:inherit}.mw-parser-output .cs1-hidden-error{display:none;color:var(--color-error,#d33)}.mw-parser-output .cs1-visible-error{color:var(--color-error,#d33)}.mw-parser-output .cs1-maint{display:none;color:#085;margin-left:0.3em}.mw-parser-output .cs1-kern-left{padding-left:0.2em}.mw-parser-output .cs1-kern-right{padding-right:0.2em}.mw-parser-output .citation .mw-selflink{font-weight:inherit}@media screen{.mw-parser-output .cs1-format{font-size:95%}html.skin-theme-clientpref-night .mw-parser-output .cs1-maint{color:#18911f}}@media screen and (prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .cs1-maint{color:#18911f}}

/* end https://en.wikipedia.org/ */

</style><cite id="CITEREFVlad_Mihalcea2015" class="citation web cs1">Vlad Mihalcea (2015-10-20). <a rel="nofollow" class="external text" href="https://vladmihalcea.com/2015/10/20/a-beginners-guide-to-read-and-write-skew-phenomena/">"A beginner's guide to read and write skew phenomena"</a>.</cite></span>

</li>

<li id="cite_note-4"><span class="mw-cite-backlink"><b><a href="#cite_ref-4">^</a></b></span> <span class="reference-text"><cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://wiki.postgresql.org/wiki/SSI#Simple_Write_Skew">"Postgresql wiki - SSI"</a>.</cite></span>

</li>

<cite class="citation web cs1"><a rel="nofollow" class="external text" href="http://www.cs.umb.edu/~poneil/iso.pdf">"A Critique of ANSI SQL Isolation Levels"</a> <span class="cs1-format">(PDF)</span><span class="reference-accessdate">. Retrieved <span class="nowrap">29 July</span> 2012</span>.</cite></span>

</li>

<li id="cite_note-6"><span class="mw-cite-backlink"><b><a href="#cite_ref-6">^</a></b></span> <span class="reference-text"><cite id="CITEREFsalesforce2010" class="citation web cs1">salesforce (2010-12-06). <a rel="nofollow" class="external text" href="https://www.youtube.com/v/7J61pPG9j90?version=3">"Customer testimonials (SimpleGeo, CLOUDSTOCK 2010)"</a>. www.DataStax.com: DataStax<span class="reference-accessdate">. Retrieved <span class="nowrap">2010-03-09</span></span>. <q>(see above at about 13:30 minutes of the webcast!)</q></cite></span>

</li>

</ol></div></div>

<div class="mw-heading mw-heading2"><h2 id="External_links">External links</h2></div>

<ul><li><a rel="nofollow" class="external text" href="http://docs.oracle.com/cd/B12037_01/server.101/b10743/toc.htm">Oracle® Database Concepts</a>, <a rel="nofollow" class="external text" href="http://docs.oracle.com/cd/B12037_01/server.101/b10743/consist.htm#sthref1919">chapter 13 Data Concurrency and Consistency, Preventable Phenomena and Transaction Isolation Levels</a></li>

<li><a rel="nofollow" class="external text" href="http://docs.oracle.com/cd/B19306_01/server.102/b14200/toc.htm">Oracle® Database SQL Reference</a>, <a rel="nofollow" class="external text" href="http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10.htm#i2068385">chapter 19 SQL Statements: SAVEPOINT to UPDATE</a>, <a rel="nofollow" class="external text" href="http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10005.htm#i2067247">SET TRANSACTION</a></li>

<li>in <a href="Java_Database_Connectivity" title="Java Database Connectivity">JDBC</a>: <a rel="nofollow" class="external text" href="http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#field_summary">Connection constant fields</a>, <a rel="nofollow" class="external text" href="http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#getTransactionIsolation()">Connection.getTransactionIsolation()</a>, <a rel="nofollow" class="external text" href="http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setTransactionIsolation(int)">Connection.setTransactionIsolation(int)</a></li>

<li>in <a href="Spring_Framework" title="Spring Framework">Spring Framework</a>: <a rel="nofollow" class="external text" href="http://static.springsource.org/spring/docs/current/javadoc-api/org/springframework/transaction/annotation/Transactional.html">@Transactional</a>, <a rel="nofollow" class="external text" href="http://static.springsource.org/spring/docs/current/javadoc-api/org/springframework/transaction/annotation/Isolation.html">Isolation</a></li>

<li><a rel="nofollow" class="external text" href="http://www.bailis.org/blog/when-is-acid-acid-rarely/">P.Bailis. When is "ACID" ACID? Rarely</a></li></ul>

/* start https://en.wikipedia.org/ */

.mw-parser-output .hlist dl,.mw-parser-output .hlist ol,.mw-parser-output .hlist ul{margin:0;padding:0}.mw-parser-output .hlist dd,.mw-parser-output .hlist dt,.mw-parser-output .hlist li{margin:0;display:inline}.mw-parser-output .hlist.inline,.mw-parser-output .hlist.inline dl,.mw-parser-output .hlist.inline ol,.mw-parser-output .hlist.inline ul,.mw-parser-output .hlist dl dl,.mw-parser-output .hlist dl ol,.mw-parser-output .hlist dl ul,.mw-parser-output .hlist ol dl,.mw-parser-output .hlist ol ol,.mw-parser-output .hlist ol ul,.mw-parser-output .hlist ul dl,.mw-parser-output .hlist ul ol,.mw-parser-output .hlist ul ul{display:inline}.mw-parser-output .hlist .mw-empty-li{display:none}.mw-parser-output .hlist dt::after{content:": "}.mw-parser-output .hlist dd::after,.mw-parser-output .hlist li::after{content:" · ";font-weight:bold}.mw-parser-output .hlist dd:last-child::after,.mw-parser-output .hlist dt:last-child::after,.mw-parser-output .hlist li:last-child::after{content:none}.mw-parser-output .hlist dd dd:first-child::before,.mw-parser-output .hlist dd dt:first-child::before,.mw-parser-output .hlist dd li:first-child::before,.mw-parser-output .hlist dt dd:first-child::before,.mw-parser-output .hlist dt dt:first-child::before,.mw-parser-output .hlist dt li:first-child::before,.mw-parser-output .hlist li dd:first-child::before,.mw-parser-output .hlist li dt:first-child::before,.mw-parser-output .hlist li li:first-child::before{content:" (";font-weight:normal}.mw-parser-output .hlist dd dd:last-child::after,.mw-parser-output .hlist dd dt:last-child::after,.mw-parser-output .hlist dd li:last-child::after,.mw-parser-output .hlist dt dd:last-child::after,.mw-parser-output .hlist dt dt:last-child::after,.mw-parser-output .hlist dt li:last-child::after,.mw-parser-output .hlist li dd:last-child::after,.mw-parser-output .hlist li dt:last-child::after,.mw-parser-output .hlist li li:last-child::after{content:")";font-weight:normal}.mw-parser-output .hlist ol{counter-reset:listitem}.mw-parser-output .hlist ol>li{counter-increment:listitem}.mw-parser-output .hlist ol>li::before{content:" "counter(listitem)"\a0 "}.mw-parser-output .hlist dd ol>li:first-child::before,.mw-parser-output .hlist dt ol>li:first-child::before,.mw-parser-output .hlist li ol>li:first-child::before{content:" ("counter(listitem)"\a0 "}

/* end https://en.wikipedia.org/ */

</style><style data-mw-deduplicate="TemplateStyles:r1236075235">

/* start https://en.wikipedia.org/ */

.mw-parser-output .navbox{box-sizing:border-box;border:1px solid #a2a9b1;width:100%;clear:both;font-size:88%;text-align:center;padding:1px;margin:1em auto 0}.mw-parser-output .navbox .navbox{margin-top:0}.mw-parser-output .navbox+.navbox,.mw-parser-output .navbox+.navbox-styles+.navbox{margin-top:-1px}.mw-parser-output .navbox-inner,.mw-parser-output .navbox-subgroup{width:100%}.mw-parser-output .navbox-group,.mw-parser-output .navbox-title,.mw-parser-output .navbox-abovebelow{padding:0.25em 1em;line-height:1.5em;text-align:center}.mw-parser-output .navbox-group{white-space:nowrap;text-align:right}.mw-parser-output .navbox,.mw-parser-output .navbox-subgroup{background-color:#fdfdfd}.mw-parser-output .navbox-list{line-height:1.5em;border-color:#fdfdfd}.mw-parser-output .navbox-list-with-group{text-align:left;border-left-width:2px;border-left-style:solid}.mw-parser-output tr+tr>.navbox-abovebelow,.mw-parser-output tr+tr>.navbox-group,.mw-parser-output tr+tr>.navbox-image,.mw-parser-output tr+tr>.navbox-list{border-top:2px solid #fdfdfd}.mw-parser-output .navbox-title{background-color:#ccf}.mw-parser-output .navbox-abovebelow,.mw-parser-output .navbox-group,.mw-parser-output .navbox-subgroup .navbox-title{background-color:#ddf}.mw-parser-output .navbox-subgroup .navbox-group,.mw-parser-output .navbox-subgroup .navbox-abovebelow{background-color:#e6e6ff}.mw-parser-output .navbox-even{background-color:#f7f7f7}.mw-parser-output .navbox-odd{background-color:transparent}.mw-parser-output .navbox .hlist td dl,.mw-parser-output .navbox .hlist td ol,.mw-parser-output .navbox .hlist td ul,.mw-parser-output .navbox td.hlist dl,.mw-parser-output .navbox td.hlist ol,.mw-parser-output .navbox td.hlist ul{padding:0.125em 0}.mw-parser-output .navbox .navbar{display:block;font-size:100%}.mw-parser-output .navbox-title .navbar{float:left;text-align:left;margin-right:0.5em}body.skin--responsive .mw-parser-output .navbox-image img{max-width:none!important}@media print{body.ns-0 .mw-parser-output .navbox{display:none!important}}

/* end https://en.wikipedia.org/ */

</style></div><div role="navigation" class="navbox authority-control" aria-label="Navbox391" style="padding:3px"><table class="nowraplinks hlist navbox-inner" style="border-spacing:0;background:transparent;color:inherit"><tbody><tr><th scope="row" class="navbox-group" style="width:1%">Authority control databases: National </th><td class="navbox-list-with-group navbox-list navbox-odd" style="width:100%;padding:0"><div style="padding:0 0.25em"><ul><li><span class="uid"><a rel="nofollow" class="external text" href="https://d-nb.info/gnd/4704495-0">Germany</a></span></li></ul></div></td></tr></tbody></table></div></div><div><div class="zim-footer">

This article is issued from <a class="external text" title="Last edited on 2025-05-03" href="https://en.wikipedia.org/wiki/?title=Isolation_(database_systems)&oldid=1288643596">Wikipedia</a>. The text is available under <a class="external text" href="https://creativecommons.org/licenses/by-sa/4.0/deed.en">Creative Commons Attribution-Share Alike 4.0</a> unless otherwise noted. Additional terms may apply for the media files.

</div>

</div></div>

</div>

</main>

</div>

</body></html>